﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using jiaju.Areas.Back.Models.DBModel;
using jiaju.Areas.Back.Models;
using System.Data.SqlClient;
namespace jiaju.Areas.Back.Models.DAL
{
    public class BannerService
    {
        public static List<Banner> getBanner(int page, int limit, string title)
        {
            String sql = string.Format("select top({0}) * from dbo.banner where Id not in (select top(({1} - 1) * {0}) Id from banner)", limit, page);
            if (!string.IsNullOrEmpty(title))
            {
                sql = sql + string.Format("and Title like '%{0}%'", title);
            }
            SqlDataReader sdr = DBHelp.ExecuteReader(sql);
            List<Banner> list = new List<Banner>();
            while (sdr.Read())
            {
                int Id = (int)sdr["Id"];
                string Title = (string)sdr["Title"];
                string ImgUrl = (string)sdr["ImgUrl"];
                string ToUrl = (string)sdr["ToUrl"];
                Banner banner = new Banner()
                {
                    Id = Id,
                    Title = Title,
                    ImgUrl = ImgUrl,
                    ToUrl = ToUrl

                };
                list.Add(banner);
            }
            return list;
        }
        public static int addBanner(Banner banner)
        {
            string sql = string.Format(@"INSERT INTO [dbo].[banner]
                               (ImgUrl ,Title,ToUrl)
                         VALUES( '{0}', '{1}','{2}' )", banner.ImgUrl, banner.Title, banner.ToUrl);
            int num = DBHelp.ExecuteNonQuery(sql);
            return num;
        }
        public static Banner GetById(string ArtId)
        {
            string sql = "select * from dbo.banner where Id=" + ArtId;
            Banner banner = null;
            SqlDataReader sdr = DBHelp.ExecuteReader(sql);
            while (sdr.Read())
            {
                int Id = (int)sdr["Id"];
                string Title = (string)sdr["Title"];
                string ImgUrl = (string)sdr["ImgUrl"];
                string ToUrl = (string)sdr["ToUrl"];
                banner = new Banner()
                {
                    Id=Id,
                    Title = Title,
                    ImgUrl = ImgUrl,
                    ToUrl=ToUrl
                };
            }
            return banner;
        }
        public static int EditBanner(Banner ban)
        {
            string sql = String.Format(@"UPDATE [dbo].[banner]
                                  SET Title ='{0}'
                                  ,ImgUrl='{1}',ToUrl='{2}'
                WHERE Id={3}", ban.Title, ban.ImgUrl,ban.ToUrl,ban.Id);
            int num = DBHelp.ExecuteNonQuery(sql);
            return num;
        }
        public static int DelBanner(String Id)
        {
            String sqlPhoto = "delete from dbo.banner where Id=" + Id;
            int num = DBHelp.ExecuteNonQuery(sqlPhoto);
            return num;
        }
        public static List<Banner> getAllTitle()
        {
            string sql = "select Id,Name from dbo.banner";
            SqlDataReader sdr = DBHelp.ExecuteReader(sql);
            List<Banner> list = new List<Banner>();
            while (sdr.Read())
            {
                int id = (int)sdr["Id"];
                string title = (string)sdr["Title"];
                Banner arc = new Banner() { Id = id, Title = title };
                list.Add(arc);
            }
            return list;
        }
    }
}